/*
select * from UPDATE_VIEW_COST_TABLE
ALTER TABLE Update_View_Cost_Table ADD Q1_Freight FLOAT DEFAULT 0.0 WITH VALUES, Q2_Freight FLOAT DEFAULT 0.0 WITH VALUES, Q3_Freight FLOAT DEFAULT 0.0 WITH VALUES, Q4_Freight FLOAT DEFAULT 0.0 WITH VALUES
EXEC SP_UPDATE_COST_TABLE 
SELECT * FROM COST_TABLE where processstep_id in (1182)
*/
ALTER PROC SP_UPDATE_COST_TABLE
AS
BEGIN
	DECLARE @BASE_PRODUCT_ID INT
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @PREV_PROCESSSTEP_ID INT
	DECLARE @PS_TYPE_ID INT
	DECLARE @Location_ID INT
	DECLARE @GROSSDIEPERWAFER INT
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @Q1_STD_YIELD NUMERIC
	DECLARE @Q2_STD_YIELD NUMERIC
	DECLARE @Q3_STD_YIELD NUMERIC
	DECLARE @Q4_STD_YIELD NUMERIC
	DECLARE @Q1_STD_COST NUMERIC(10,4)
	DECLARE @Q2_STD_COST NUMERIC(10,4)
	DECLARE @Q3_STD_COST NUMERIC(10,4)
	DECLARE @Q4_STD_COST NUMERIC(10,4)
	DECLARE @Q1_STD_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q2_STD_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q3_STD_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q4_STD_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q1_STD_COST_PRE_DIE_PREV_STEP NUMERIC(10,4)
	DECLARE @Q2_STD_COST_PRE_DIE_PREV_STEP NUMERIC(10,4)
	DECLARE @Q3_STD_COST_PRE_DIE_PREV_STEP NUMERIC(10,4)
	DECLARE @Q4_STD_COST_PRE_DIE_PREV_STEP NUMERIC(10,4)
	DECLARE @Q1_INDV_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q2_INDV_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q3_INDV_COST_PER_DIE NUMERIC(10,4)
	DECLARE @Q4_INDV_COST_PER_DIE NUMERIC(10,4)
	DECLARE @CURRENT_YEAR INT

	SET @CURRENT_YEAR = YEAR( GETDATE() )

	DECLARE @RESULTS_TABLE TABLE ( 	PROCESSSTEP_ID INT,
					PRODUCT_ID INT,
					PRODUCT VARCHAR(50),
					PS_TYPE VARCHAR(50),
					PS_TYPE_ID INT,
					LOCATION_ID INT,
					LOCATION_NAME VARCHAR(50),
					STD_COST FLOAT,
					COST_PER_DIE FLOAT ) 

	DECLARE @BASE_PRODUCTS_CURSOR CURSOR
	DECLARE @PRODUCTS_CURSOR CURSOR
		
	DELETE FROM COST_TABLE
	
	/* LOAD ONLY BASE PRODUCT-IDS */
	SET @BASE_PRODUCTS_CURSOR = CURSOR FOR 
	SELECT PRODUCT_ID FROM PRODUCT WHERE ( P_PARENT_PRODUCT_ID IS NULL OR P_PARENT_PRODUCT_ID = -1) AND ( P_TYPE IS NULL OR P_TYPE = 'BOARD' OR P_TYPE = 'KIT' ) --AND PRODUCT_ID = 172

	OPEN @BASE_PRODUCTS_CURSOR 
	FETCH NEXT FROM @BASE_PRODUCTS_CURSOR INTO @BASE_PRODUCT_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @PRODUCTS_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID, Process_TYPE_NAME,PS_TYPE_ID, Location_Id FROM fn_get_process_flow_by_order ( @BASE_PRODUCT_ID )
		
		OPEN @PRODUCTS_CURSOR
		FETCH NEXT FROM @PRODUCTS_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE_NAME, @PS_TYPE_ID, @Location_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @Q1_STD_COST = 0.00
			SET @Q2_STD_COST = 0.00
			SET @Q3_STD_COST = 0.00
			SET @Q4_STD_COST = 0.00
			SET @Q1_STD_COST_PER_DIE = 0.00 
			SET @Q2_STD_COST_PER_DIE = 0.00
			SET @Q3_STD_COST_PER_DIE = 0.00
			SET @Q4_STD_COST_PER_DIE = 0.00
			SET @Q1_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q2_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q3_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q4_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q1_STD_YIELD = 100.00
			SET @Q2_STD_YIELD = 100.00
			SET @Q3_STD_YIELD = 100.00
			SET @Q4_STD_YIELD = 100.00

			SELECT 
			@Q1_STD_COST = ( ISNULL( Q1_STANDARD_COST, 0.00 ) + ISNULL( Q1_FIXED_OVER_HEAD_COST, 0.00 ) + ISNULL( Q1_MISC_COST, 0.00 ) ), --+ ISNULL( Q1_FREIGHT, 0.00 ) ),
			@Q1_STD_YIELD = ISNULL( Q1_YIELD, 100),
			@Q2_STD_COST = ( ISNULL( Q2_STANDARD_COST, 0.00 ) + ISNULL( Q2_FIXED_OVER_HEAD_COST, 0.00 ) + ISNULL( Q2_MISC_COST, 0.00 ) ), --+ ISNULL( Q2_FREIGHT, 0.00 ) ),
			@Q2_STD_YIELD = ISNULL( Q2_YIELD, 100),
			@Q3_STD_COST = ( ISNULL( Q3_STANDARD_COST, 0.00 ) + ISNULL( Q3_FIXED_OVER_HEAD_COST, 0.00 ) + ISNULL( Q3_MISC_COST, 0.00 ) ), --+ ISNULL( Q3_FREIGHT, 0.00 ) ),
			@Q3_STD_YIELD = ISNULL( Q3_YIELD, 100),
			@Q4_STD_COST = ( ISNULL( Q4_STANDARD_COST, 0.00 ) + ISNULL( Q4_FIXED_OVER_HEAD_COST, 0.00 ) + ISNULL( Q4_MISC_COST, 0.00 ) ), --+ ISNULL( Q4_FREIGHT, 0.00 ) ), 
			@Q4_STD_YIELD = ISNULL( Q4_YIELD, 100)
			FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @Location_ID AND YEAR( FIN_YEAR ) = @CURRENT_YEAR

			/*IF( @Q1_STD_YIELD IS NOT NULL AND @Q1_STD_YIELD != 0.00 )
			BEGIN
				SET @Q1_STD_COST = ( @Q1_STD_COST * 100 )/ @Q1_STD_YIELD
			END

			IF( @Q2_STD_YIELD IS NOT NULL AND @Q2_STD_YIELD != 0.00 )
			BEGIN
				SET @Q2_STD_COST = ( @Q2_STD_COST * 100 )/ @Q2_STD_YIELD
			END

			IF( @Q3_STD_YIELD IS NOT NULL AND @Q3_STD_YIELD != 0.00 )
			BEGIN
				SET @Q3_STD_COST = ( @Q3_STD_COST * 100 )/ @Q3_STD_YIELD
			END

			IF( @Q4_STD_YIELD IS NOT NULL AND @Q4_STD_YIELD != 0.00 )
			BEGIN
				SET @Q4_STD_COST = ( @Q4_STD_COST * 100 )/ @Q4_STD_YIELD
			END*/

			SELECT @GROSSDIEPERWAFER = ISNULL( PARAMETER_VALUE, 1 ) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = ( SELECT TOP 1 PROCESSSTEP_ID FROM 
				FN_GET_PROCESS_FLOW_BACKWARD_BY_ID ( @PROCESSSTEP_ID ) WHERE Process_Type_Name = 'Fab' ) AND PARAMETER_KEY = 'Gross Die/Wafer'

			IF( @GROSSDIEPERWAFER IS NULL )
			BEGIN
				SET @GROSSDIEPERWAFER = 1
			END
		
			IF( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'DIEBANK' OR @PS_TYPE_NAME = 'WAFER STORE' )
			BEGIN
				IF( @Q1_STD_COST IS NOT NULL AND @Q1_STD_COST != 0.00 AND @GROSSDIEPERWAFER > 0 )
				BEGIN
					SET @Q1_STD_COST_PER_DIE = @Q1_STD_COST / @GROSSDIEPERWAFER
				END
				ELSE
				BEGIN
					SET @Q1_STD_COST_PER_DIE = 0.00
				END

				IF( @Q2_STD_COST IS NOT NULL AND @Q2_STD_COST != 0.00 AND @GROSSDIEPERWAFER > 0 )
				BEGIN
					SET @Q2_STD_COST_PER_DIE = @Q2_STD_COST / @GROSSDIEPERWAFER
				END
				ELSE
				BEGIN
					SET @Q2_STD_COST_PER_DIE = 0.00
				END

				IF( @Q3_STD_COST IS NOT NULL AND @Q3_STD_COST != 0.00 AND @GROSSDIEPERWAFER > 0 )
				BEGIN
					SET @Q3_STD_COST_PER_DIE = @Q3_STD_COST / @GROSSDIEPERWAFER
				END
				ELSE
				BEGIN
					SET @Q3_STD_COST_PER_DIE = 0.00
				END

				IF( @Q4_STD_COST IS NOT NULL AND @Q4_STD_COST != 0.00 AND @GROSSDIEPERWAFER > 0 )
				BEGIN
					SET @Q4_STD_COST_PER_DIE = @Q4_STD_COST / @GROSSDIEPERWAFER
				END
				ELSE
				BEGIN
					SET @Q4_STD_COST_PER_DIE = 0.00
				END
			END
			ELSE
			BEGIN
				SET @Q1_STD_COST_PER_DIE = @Q1_STD_COST
				SET @Q2_STD_COST_PER_DIE = @Q2_STD_COST
				SET @Q3_STD_COST_PER_DIE = @Q3_STD_COST
				SET @Q4_STD_COST_PER_DIE = @Q4_STD_COST
			END

			
			SELECT TOP 1 @PREV_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID ( @PROCESSSTEP_ID ) WHERE PROCESSSTEP_ID != @PROCESSSTEP_ID

			SELECT TOP 1 @Q1_STD_COST_PRE_DIE_PREV_STEP = Q1_CUMM_STD_COST_PER_DIE, 
			@Q2_STD_COST_PRE_DIE_PREV_STEP = Q2_CUMM_STD_COST_PER_DIE, 
			@Q3_STD_COST_PRE_DIE_PREV_STEP = Q3_CUMM_STD_COST_PER_DIE,
			@Q4_STD_COST_PRE_DIE_PREV_STEP = Q4_CUMM_STD_COST_PER_DIE FROM COST_TABLE WHERE PROCESSSTEP_ID = @PREV_PROCESSSTEP_ID

			/*IF @PROCESSSTEP_ID = 1163
			BEGIN
				PRINT '@GROSSDIEPERWAFER  ' + CONVERT ( VARCHAR, @GROSSDIEPERWAFER )
				PRINT '@Q3_STD_COST_PER_DIE  ' + CONVERT ( VARCHAR, @Q3_STD_COST_PER_DIE )
				PRINT '@Q3_STD_COST_PRE_DIE_PREV_STEP  ' + CONVERT ( VARCHAR, @Q3_STD_COST_PRE_DIE_PREV_STEP )
			END*/


			/*SET @Q1_STD_COST_PER_DIE = ISNULL( @Q1_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q1_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			SET @Q2_STD_COST_PER_DIE = ISNULL( @Q2_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q2_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			SET @Q3_STD_COST_PER_DIE = ISNULL( @Q3_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q3_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			SET @Q4_STD_COST_PER_DIE = ISNULL( @Q4_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q4_STD_COST_PRE_DIE_PREV_STEP, 0.00 ) */
			--***********************
			
			IF( @Q1_STD_YIELD IS NOT NULL AND @Q1_STD_YIELD != 0.00 )
			BEGIN
				SET @Q1_INDV_COST_PER_DIE =  ISNULL( @Q1_STD_COST_PER_DIE, 0.00 )  * 100 /@Q1_STD_YIELD 
				SET @Q1_STD_COST_PER_DIE = ( ISNULL( @Q1_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q1_STD_COST_PRE_DIE_PREV_STEP, 0.00 ) ) * 100 /@Q1_STD_YIELD 
			END
			ELSE
			BEGIN
				SET @Q1_INDV_COST_PER_DIE =  ISNULL( @Q1_STD_COST_PER_DIE, 0.00 )  
				SET @Q1_STD_COST_PER_DIE = ISNULL( @Q1_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q1_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			END

			IF( @Q2_STD_YIELD IS NOT NULL AND @Q2_STD_YIELD != 0.00 )
			BEGIN
				SET @Q2_INDV_COST_PER_DIE =  ISNULL( @Q2_STD_COST_PER_DIE, 0.00 )  * 100 /@Q2_STD_YIELD 
				SET @Q2_STD_COST_PER_DIE =  ( ISNULL( @Q2_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q2_STD_COST_PRE_DIE_PREV_STEP, 0.00 ) ) * 100 /@Q2_STD_YIELD 
			END
			ELSE
			BEGIN
				SET @Q2_INDV_COST_PER_DIE =  ISNULL( @Q2_STD_COST_PER_DIE, 0.00 ) 
				SET @Q2_STD_COST_PER_DIE = ISNULL( @Q2_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q2_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			END

			IF( @Q3_STD_YIELD IS NOT NULL AND @Q3_STD_YIELD != 0.00 )
			BEGIN
				SET @Q3_INDV_COST_PER_DIE =  ISNULL( @Q3_STD_COST_PER_DIE, 0.00 )  * 100 /@Q3_STD_YIELD 
				SET @Q3_STD_COST_PER_DIE =  ( ISNULL( @Q3_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q3_STD_COST_PRE_DIE_PREV_STEP, 0.00 ) ) * 100 /@Q3_STD_YIELD 
			END
			ELSE
			BEGIN
				SET @Q3_INDV_COST_PER_DIE =  ISNULL( @Q3_STD_COST_PER_DIE, 0.00 )
				SET @Q3_STD_COST_PER_DIE = ISNULL( @Q3_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q3_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			END

			IF( @Q4_STD_YIELD IS NOT NULL AND @Q4_STD_YIELD != 0.00 )
			BEGIN
				SET @Q4_INDV_COST_PER_DIE =  ISNULL( @Q4_STD_COST_PER_DIE, 0.00 )  * 100 /@Q4_STD_YIELD
				SET @Q4_STD_COST_PER_DIE =  ( ISNULL( @Q4_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q4_STD_COST_PRE_DIE_PREV_STEP, 0.00 ) ) * 100 /@Q4_STD_YIELD 
			END
			ELSE
			BEGIN
				SET @Q4_INDV_COST_PER_DIE =  ISNULL( @Q4_STD_COST_PER_DIE, 0.00 )
				SET @Q4_STD_COST_PER_DIE = ISNULL( @Q4_STD_COST_PER_DIE, 0.00 ) + ISNULL( @Q4_STD_COST_PRE_DIE_PREV_STEP, 0.00 )
			END

			--**********************


			--INSERT INTO [COST_TABLE]([PROCESSSTEP_ID], [LOCATION_ID], [Q1_CUMM_STD_COST], [Q1_CUMM_STD_COST_PER_DIE], [Q2_CUMM_STD_COST], [Q2_CUMM_STD_COST_PER_DIE], [Q3_CUMM_STD_COST], [Q3_CUMM_STD_COST_PER_DIE], [Q4_CUMM_STD_COST], [Q4_CUMM_STD_COST_PER_DIE])
			--VALUES( @PROCESSSTEP_ID, @Location_ID, @Q1_STD_COST, @Q1_curr_cumm_COST_PER_DIE, @Q2_STD_COST, @Q2_STD_COST_PER_DIE, @Q3_STD_COST, @Q3_STD_COST_PER_DIE, @Q4_STD_COST, @Q4_STD_COST_PER_DIE)
			INSERT INTO [COST_TABLE]([PROCESSSTEP_ID], [LOCATION_ID], [Q1_CUMM_STD_COST], [Q1_CUMM_STD_COST_PER_DIE],[Q1_STD_COST_PER_DIE],
											[Q2_CUMM_STD_COST], [Q2_CUMM_STD_COST_PER_DIE],[Q2_STD_COST_PER_DIE],
											[Q3_CUMM_STD_COST], [Q3_CUMM_STD_COST_PER_DIE],[Q3_STD_COST_PER_DIE],
											[Q4_CUMM_STD_COST], [Q4_CUMM_STD_COST_PER_DIE],[Q4_STD_COST_PER_DIE] )
			VALUES( @PROCESSSTEP_ID, @LOCATION_ID,  @Q1_STD_COST, @Q1_STD_COST_PER_DIE, @Q1_INDV_COST_PER_DIE,
								     @Q2_STD_COST, @Q2_STD_COST_PER_DIE, @Q2_INDV_COST_PER_DIE,
								     @Q3_STD_COST, @Q3_STD_COST_PER_DIE, @Q3_INDV_COST_PER_DIE,
								     @Q4_STD_COST, @Q4_STD_COST_PER_DIE, @Q4_INDV_COST_PER_DIE)

			SET @Q1_STD_COST = 0.00
			SET @Q2_STD_COST = 0.00
			SET @Q3_STD_COST = 0.00
			SET @Q4_STD_COST = 0.00
			SET @Q1_STD_COST_PER_DIE = 0.00 
			SET @Q2_STD_COST_PER_DIE = 0.00
			SET @Q3_STD_COST_PER_DIE = 0.00
			SET @Q4_STD_COST_PER_DIE = 0.00
			SET @Q1_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q2_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q3_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q4_STD_COST_PRE_DIE_PREV_STEP = 0.00
			SET @Q1_STD_YIELD = 100.00
			SET @Q2_STD_YIELD = 100.00
			SET @Q3_STD_YIELD = 100.00
			SET @Q4_STD_YIELD = 100.00
			SET @GROSSDIEPERWAFER = NULL
			SET @PREV_PROCESSSTEP_ID = NULL
			SET @Q1_INDV_COST_PER_DIE = 0.00
			SET @Q2_INDV_COST_PER_DIE = 0.00
			SET @Q3_INDV_COST_PER_DIE = 0.00
			SET @Q4_INDV_COST_PER_DIE = 0.00
			
			FETCH NEXT FROM @PRODUCTS_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE_NAME, @PS_TYPE_ID, @Location_ID
		END
		CLOSE @PRODUCTS_CURSOR
		DEALLOCATE @PRODUCTS_CURSOR
	
		FETCH NEXT FROM @BASE_PRODUCTS_CURSOR INTO @BASE_PRODUCT_ID
	END
	CLOSE @BASE_PRODUCTS_CURSOR
	DEALLOCATE @BASE_PRODUCTS_CURSOR
END
